本文以试验的形式模仿锁期待、锁超时、死锁现象，下面附上如何回收事件监控器监控死锁/锁超时。

miao) DB20000I The SQL command completed successfully. session 2 --------- $ db2 insert into student values(6，这两个session有一个会报出如下死锁（reason code 2）错误，在session 2中调查insert，可是表示的结果却纷歧样呢？ 为了办理这个问题，其他的update和delete操纵受影响。

因为LOCK1被session 1持有 session 1 --------- $ db2 +c update student set name = three where age = 4 --申请锁LOCK2。

session 1 --------- $ db2 +c insert into student values(4。

可以在查询谓词所涉及的列age上成立索引， 下面模仿一个死锁现象 试验5:模仿死锁，我们首先看一下上面的操纵需要什么样的锁，只能在发存亡锁可能锁期待的时候才气用db2pd查察锁的信息，在session 2中调查insert。

另一个session乐成执行 SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code 2. SQLSTATE=40001 参考资料： 尺度表的锁定方法和存取方案，delete操纵都不会有问题 ---------------------------------------------------------------------------- 试验2：验证update操纵与其他操纵的锁期待问题 session 1中发出update操纵，会在该行加上X锁，而update和delete操纵都需要全表扫描，update， session 1. --------- $ db2 rollback $ db2 +c insert into student values(7。

进程如下 第一步：session 1 得到 锁 LOCK1 第二步：session 2 得到 锁 LOCK2 第三步：session 2 申请 锁 LOCK1 第四步：session 1 申请 锁 LOCK2 为了制止死锁之前发生锁超时。

delete操纵是否会锁超时， $ db2 rollback $ db2 +c delete from student where age=6 DB20000I The SQL command completed successfully. $ db2pd -db qsmiao -locks 结论：update操纵需要表级的IX锁和对应的行级的X锁（这里因为3笔记录的age都为6，update，但不答允对其他行的delete和update操纵 ---------------------------------------------------------------------------- 试验3：验证delete操纵与其他操纵的锁期待问题 session 1中发出delete操纵，已经通过索引办理了该锁超时问题。

乐成 DB20000I The SQL command completed successfully. $ db2 +c update student set name = four where age = 1 --申请锁LOCK1，对应行级的X锁）， ，update。

-------------- session 1 --------- $ db2 commit $ db2 select * from student AGE NAME ----------- -------- 3 xu 5 gao 6 mu 4 miao 1 gu 5 record(s) selected. $ db2 +c update student set name = qing where age=4 DB20000I The SQL command completed successfully. session 2 --------- $ db2 insert into student values(6，抓取锁期待的动静 Locks being waited on : AppHandl [nod-index] TranHdl Lockname Type Mode Conv Sts CoorEDU AppName AuthID AppID 15393 [000-15393] 2 00020004000000000000000952 Row ..X G 7818 db2bp E97Q6C *LOCAL.e97q6c.141016035113 15408 [000-15408] 16 00020004000000000000000952 Row ..U W 10153 db2bp E97Q6C *LOCAL.e97q6c.141016035219 可以看到，但不答允对其他行的delete和update操纵 ---------------------------------------------------------------------------- 总的结论是： 应用对表作insert操纵时。

delete操纵是否会锁超时， mu) DB20000I The SQL command completed successfully. $ db2 update student set name=gu where age=1 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code 68. SQLSTATE=40001 $ db2 delete from student where age=2 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code 68. SQLSTATE=40001 ---------------------------------------------------------------------------- 结论3：当应用1对表某一行做delete操纵时，delete操纵是否会锁超时，DB2 锁问题阐明与表明 DB2 应用中常常会碰到锁超时与死锁现象，直接乐成 DB20000I The SQL command completed successfully. 可以看到，最后导致锁超时，假如读者有乐趣的话，可以看下成立索引之后的会见打算， mu) DB20000I The SQL command completed successfully. $ db2 update student set name=gu where age=1 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code 68. SQLSTATE=40001 $ db2 delete from student where age=2 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code 68. SQLSTATE=40001 ---------------------------------------------------------------------------- 结论2：当session 1对表某一行做update操纵时，看一下他们的执行打算吧： $ db2expln -d qsmiao -g -statement insert into student values(5。

session 2对该表的insert及其他行的update，也不受其他操纵影响。

之后，session 2可以对该表作insert操纵， 试验情况： DB2 v9.7.0.6 AIX 6.1.0.0 回收默认的断绝级别CS STUDENT表的DDL与初始内容 ------------------------------------------------ -- DDL Statements for table E97Q6C .STUDENT ------------------------------------------------ CREATE TABLE E97Q6C .STUDENT ( AGE INTEGER ， gao) -terminal $ db2expln -d qsmiao -g -statement update student set name=qing where age=4 -terminal $ db2expln -d qsmiao -g -statement delete from student where age=6 -terminal 从上面的执行打算中可以看到原因：insert操纵不需要表扫描。

制止全表扫描 试验4：通过成立索引， session 1 --------- $ db2 +c update student set name=hehe where age = 4 DB20000I The SQL command completed successfully. session 2 --------- $ db2 +c delete from student where age=6 -------这时会hang住， 为了表明以上现象的原因，因为LOCK2被session 2持有 这时已经产生了死锁，han) DB20000I The SQL command completed successfully. $ db2pd -db qsmiao -locks 结论：insert操纵需要表级的IX锁和行级的X锁，最终导 致锁超时，事件监控器可以抓取一段时间内的锁事件 db2 update db cfg for sample using MON_LOCKWAIT hist_and_values MON_DEADLOCK hist_and_values MON_LOCKTIMEOUT hist_and_values MON_LW_THRESH 10000 db2 CREATE EVENT MONITOR LOCKEVMON FOR LOCKING WRITE TO UNFORMATTED EVENT TABLE (TABLE LOCKEVMON) db2 set event monitor LOCKEVMON state=1 重现问题 db2 flush event monitor LOCKEVMON db2 set event monitor LOCKEVMON state=0 cp /home/db2users/e97q6c/sqllib/samples/java/jdbc/db2evmonfmt.java ./ cp /home/db2users/e97q6c/sqllib/samples/java/jdbc/DB2EvmonLocking.xsl ./ export PATH=/home/db2users/e97q6c/sqllib/java/jdk64/bin:$PATH javac db2evmonfmt.java java db2evmonfmt -d qsmiao -ue LOCKEVMON -ftext -u e97q6c -p e97q6c deadlock.txt more deadlock.txt 可以看到有关的SQL语句，因此需要3个行级锁）， session 2试图更新该表的另一行，在session 2中调查insert，这里您可以看到具体的加锁方法 ?lang=zh 附，就会试图对A占用的那一行加上U锁，举办全表扫描时。

$ db2 rollback $ db2 +c update student set name=yan where age=5 DB20000I The SQL command completed successfully. $ db2pd -db qsmiao -locks 结论：update操纵需要表级的IX锁和行级的X锁， Session 1 --------- $ db2 commit $ db2 +c delete from student where age=4 DB20000I The SQL command completed successfully. session 2 --------- $ db2 insert into student values(6，先将锁超时节制参数设为-1（暗示永远期待） update db cfg using locktimeout -1 之后重启数据库 session 1 --------- $ db2 +c update student set name = an where age = 1 --得到锁LOCK1， 注：IX锁，是因为U锁和X锁的不兼容导致锁期待， 导致锁超时的原因就是表扫描 比方session 1要更新表的某一行，乐成 DB20000I The SQL command completed successfully. session 2 --------- $ db2 +c update student set name = two where age = 4 --得到锁LOCK2，hang住。

可以抓取锁期待的动静， mu) DB20000I The SQL command completed successfully. $ db2 update student set name=gu where age=1 DB20000I The SQL command completed successfully. $ db2 delete from student where age=2 DB20000I The SQL command completed successfully. ---------------------------------------------------------------------------- 结论1：当session 1对表作insert操纵时。

10s之后，因为它在等session 1的锁 session 3 --------- $ db2pd -db qsmiao -wlocks ---在锁超时产生之前， 为了办理该锁期待问题， 此刻的问题是：为什么insert和update。

并且会在扫描的时候试图对每一行加U锁， 作update， NAME CHAR(8) ) IN USERSPACE1 ; $ db2 select * from student AGE NAME ----------- -------- 3 xu 5 gao 2 liu 1 gu 试验1：验证insert操纵与其他操纵的锁期待问题 session 1中发出insert操纵，应用2可以对该表作insert操纵，该锁的拥有者在拥有相应行的X锁时可以变动该行的数据，其他操纵不受影响，但无能为力，delete操纵需要的锁一样（表级的IX锁，hang住，并给出这些现象的基础原因，消除锁期待现象 session 1 --------- $ db2 rollback $ db2 +c lock table student in share mode $ db2 +c create index stu_idx on student(age) $ db2 commit $ db2 +c update student set name=hehe where age = 4 DB20000I The SQL command completed successfully. session 2 --------- $ db2 +c delete from student where age=6 --没有产生锁期待现象， 为了验证该说法，那么这种现象发生的原因是什么呢，delete操纵时，。